cv_states <- as.data.frame(data.table::fread(("https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-states.csv")))
state_pops <- as.data.frame(data.table::fread(("https://raw.githubusercontent.com/COVID19Tracking/associated-data/master/us_census_data/us_census_2018_population_estimates_states.csv")))
state_pops$abb <- state_pops$state
state_pops$state <- state_pops$state_name
state_pops$state_name <- NULL
cv_states_pop <- merge(cv_states, state_pops, by= "state")Lab 11
Lab 11
1. Read in the Data
2. Look at the Data
dim(cv_states_pop)[1] 58094 9head(cv_states_pop) state date fips cases deaths geo_id population pop_density abb
1 Alabama 2023-01-04 1 1587224 21263 1 4887871 96.50939 AL
2 Alabama 2020-04-25 1 6213 213 1 4887871 96.50939 AL
3 Alabama 2023-02-26 1 1638348 21400 1 4887871 96.50939 AL
4 Alabama 2022-12-03 1 1549285 21129 1 4887871 96.50939 AL
5 Alabama 2020-05-06 1 8691 343 1 4887871 96.50939 AL
6 Alabama 2021-04-21 1 524367 10807 1 4887871 96.50939 ALtail(cv_states_pop) state date fips cases deaths geo_id population pop_density abb
58089 Wyoming 2022-09-11 56 175290 1884 56 577737 5.950611 WY
58090 Wyoming 2022-08-21 56 173487 1871 56 577737 5.950611 WY
58091 Wyoming 2021-01-26 56 51152 596 56 577737 5.950611 WY
58092 Wyoming 2021-02-21 56 53795 662 56 577737 5.950611 WY
58093 Wyoming 2021-08-22 56 70671 809 56 577737 5.950611 WY
58094 Wyoming 2021-03-20 56 55581 693 56 577737 5.950611 WYstr(cv_states_pop)'data.frame': 58094 obs. of 9 variables:
$ state : chr "Alabama" "Alabama" "Alabama" "Alabama" ...
$ date : IDate, format: "2023-01-04" "2020-04-25" ...
$ fips : int 1 1 1 1 1 1 1 1 1 1 ...
$ cases : int 1587224 6213 1638348 1549285 8691 524367 1321892 1088370 1153149 814025 ...
$ deaths : int 21263 213 21400 21129 343 10807 19676 16756 16826 15179 ...
$ geo_id : int 1 1 1 1 1 1 1 1 1 1 ...
$ population : int 4887871 4887871 4887871 4887871 4887871 4887871 4887871 4887871 4887871 4887871 ...
$ pop_density: num 96.5 96.5 96.5 96.5 96.5 ...
$ abb : chr "AL" "AL" "AL" "AL" ...The merged file has 58094 observations of 9 variables. All variables are in the correct format, except the date is stored as an IDate instead of a date structure. The merged file has saved the data in alphabetical order with Alabama being the head of data and Wyoming being the tail of the data. Within each state, the data is not sorted by date either.
3. Format the Data
# format the date
cv_states_pop$date <- as.Date(cv_states_pop$date, format="%Y-%m-%d")
# format the state and state abbreviation (abb) variables
state_list <- unique(cv_states_pop$state)
cv_states_pop$state <- factor(cv_states_pop$state, levels = state_list)
abb_list <- unique(cv_states_pop$abb)
cv_states_pop$abb <- factor(cv_states_pop$abb, levels = abb_list)
### FINISH THE CODE HERE
# order the data first by state, second by date
ordered_states <- cv_states_pop[order(cv_states_pop$state, cv_states_pop$date), ]
# Confirm the variables are now correctly formatted
str(ordered_states)'data.frame': 58094 obs. of 9 variables:
$ state : Factor w/ 52 levels "Alabama","Alaska",..: 1 1 1 1 1 1 1 1 1 1 ...
$ date : Date, format: "2020-03-13" "2020-03-14" ...
$ fips : int 1 1 1 1 1 1 1 1 1 1 ...
$ cases : int 6 12 23 29 39 51 78 106 131 157 ...
$ deaths : int 0 0 0 0 0 0 0 0 0 0 ...
$ geo_id : int 1 1 1 1 1 1 1 1 1 1 ...
$ population : int 4887871 4887871 4887871 4887871 4887871 4887871 4887871 4887871 4887871 4887871 ...
$ pop_density: num 96.5 96.5 96.5 96.5 96.5 ...
$ abb : Factor w/ 52 levels "AL","AK","AZ",..: 1 1 1 1 1 1 1 1 1 1 ...head(ordered_states) state date fips cases deaths geo_id population pop_density abb
1029 Alabama 2020-03-13 1 6 0 1 4887871 96.50939 AL
597 Alabama 2020-03-14 1 12 0 1 4887871 96.50939 AL
282 Alabama 2020-03-15 1 23 0 1 4887871 96.50939 AL
12 Alabama 2020-03-16 1 29 0 1 4887871 96.50939 AL
266 Alabama 2020-03-17 1 39 0 1 4887871 96.50939 AL
78 Alabama 2020-03-18 1 51 0 1 4887871 96.50939 ALtail(ordered_states) state date fips cases deaths geo_id population pop_density abb
57902 Wyoming 2023-03-18 56 185640 2009 56 577737 5.950611 WY
57916 Wyoming 2023-03-19 56 185640 2009 56 577737 5.950611 WY
57647 Wyoming 2023-03-20 56 185640 2009 56 577737 5.950611 WY
57867 Wyoming 2023-03-21 56 185800 2014 56 577737 5.950611 WY
58057 Wyoming 2023-03-22 56 185800 2014 56 577737 5.950611 WY
57812 Wyoming 2023-03-23 56 185800 2014 56 577737 5.950611 WY# Inspect the range values for each variable. What is the date range? The range of cases and deaths?
summary(ordered_states) state date fips cases
Washington : 1158 Min. :2020-01-21 Min. : 1.00 Min. : 1
Illinois : 1155 1st Qu.:2020-12-06 1st Qu.:16.00 1st Qu.: 112125
California : 1154 Median :2021-09-11 Median :29.00 Median : 418120
Arizona : 1153 Mean :2021-09-10 Mean :29.78 Mean : 947941
Massachusetts: 1147 3rd Qu.:2022-06-17 3rd Qu.:44.00 3rd Qu.: 1134318
Wisconsin : 1143 Max. :2023-03-23 Max. :72.00 Max. :12169158
(Other) :51184
deaths geo_id population pop_density
Min. : 0 Min. : 1.00 Min. : 577737 Min. : 1.292
1st Qu.: 1598 1st Qu.:16.00 1st Qu.: 1805832 1st Qu.: 43.659
Median : 5901 Median :29.00 Median : 4468402 Median : 107.860
Mean : 12553 Mean :29.78 Mean : 6397965 Mean : 423.031
3rd Qu.: 15952 3rd Qu.:44.00 3rd Qu.: 7535591 3rd Qu.: 229.511
Max. :104277 Max. :72.00 Max. :39557045 Max. :11490.120
NA's :1106
abb
WA : 1158
IL : 1155
CA : 1154
AZ : 1153
MA : 1147
WI : 1143
(Other):51184 range(ordered_states$date)[1] "2020-01-21" "2023-03-23"range(ordered_states$cases)[1] 1 12169158range(ordered_states$deaths)[1] 0 104277Now that the variables are correctly formatted, I will investigate the ranges of these variables. The dates range from Jan 21, 2020 to March 3, 2023 which is just over 3 years. The range of cases is from 0 to 12,169,158. Finally, the range of deaths is from 0 to 104,277.
4. Add new cases, new deaths, and correct for outliers
for (i in 1:length(state_list)) {
cv_subset = subset(ordered_states, state == state_list[i])
cv_subset = cv_subset[order(cv_subset$date),]
# add starting level for new cases and deaths
cv_subset$new_cases = cv_subset$cases[1]
cv_subset$new_deaths = cv_subset$deaths[1]
### FINISH THE CODE HERE
for (j in 2:nrow(cv_subset)) {
cv_subset$new_cases[j] = cv_subset$cases[j] - cv_subset$cases[j-1]
cv_subset$new_deaths[j] = cv_subset$deaths[j] - cv_subset$deaths[j-1]
}
# include in main dataset
ordered_states$new_cases[ordered_states$state==state_list[i]] = cv_subset$new_cases
ordered_states$new_deaths[ordered_states$state==state_list[i]] = cv_subset$new_deaths
}
recent <- ordered_states |> dplyr::filter(date >= "2021-06-01")
library(ggplot2)
library(plotly)
# Inspect outliers in new_cases using plotly
p1<-ggplot(recent, aes(x = date, y = new_cases, color = state)) + geom_line() + geom_point(size = .5, alpha = 0.5)
ggplotly(p1)p1<-NULL # to clear from workspace
p2<-ggplot(recent, aes(x = date, y = new_deaths, color = state)) + geom_line() + geom_point(size = .5, alpha = 0.5)
ggplotly(p2)p2<-NULL # to clear from workspaceThere are a few cases in which states have a negative value for new cases. This happens in Florida, Colorado, Tennessee, Kentucky, and a few others. There are also instances of negative values for new deaths in some states. Massachusetts, California, Colorado, and a few others have negative values for new deaths at some point in the plot. These values are not possible and need to be fixed.
# set negative new case or death counts to 0
recent$new_cases[recent$new_cases<0] = 0
recent$new_deaths[recent$new_deaths<0] = 0
# Recalculate `cases` and `deaths` as cumulative sum of updated `new_cases` and `new_deaths`
for (i in 1:length(state_list)) {
cv_subset = subset(recent, state == state_list[i])
# add starting level for new cases and deaths
cv_subset$cases = cv_subset$cases[1]
cv_subset$deaths = cv_subset$deaths[1]
### FINISH CODE HERE
for (j in 2:nrow(cv_subset)) {
cv_subset$cases[j] = cv_subset$new_cases[j] + cv_subset$new_cases[j-1]
cv_subset$deaths[j] = cv_subset$new_deaths[j] + cv_subset$new_cases[j-1]
}
# include in main dataset
recent$cases[recent$state==state_list[i]] = cv_subset$cases
recent$deaths[recent$state==state_list[i]] = cv_subset$deaths
}
# Smooth new counts
recent$new_cases = zoo::rollmean(recent$new_cases, k=7, fill=NA, align='right') %>% round(digits = 0)
recent$new_deaths = zoo::rollmean(recent$new_deaths, k=7, fill=NA, align='right') %>% round(digits = 0)
p2<-ggplot(recent, aes(x = date, y = new_deaths, color = state)) + geom_line() + geom_point(size = .5, alpha = 0.5)
ggplotly(p2)p2<-NULLAfter recalculating and smoothing the data it is much easier to identify the trends in the graph, and all data looks like it could have been a reasonable value.
5. Add additional variables
# add population normalized (by 100,000) counts for each variable
recent$per100k = as.numeric(format(round(recent$cases/(recent$population/100000),1),nsmall=1))
recent$newper100k = as.numeric(format(round(recent$new_cases/(recent$population/100000),1),nsmall=1))
recent$deathsper100k = as.numeric(format(round(recent$deaths/(recent$population/100000),1),nsmall=1))
recent$newdeathsper100k = as.numeric(format(round(recent$new_deaths/(recent$population/100000),1),nsmall=1))
# add a naive_CFR variable = deaths / cases
recent = recent |> mutate(naive_CFR = round((deaths*100/cases),2))
# create a `cv_states_today` variable
cv_states_today = subset(recent, date==max(recent$date))6. Explore scatterplots using plotly
# pop_density vs. cases
cv_states_today |>
plot_ly(x = ~pop_density, y = ~cases,
type = 'scatter', mode = 'markers', color = ~state,
size = ~population, sizes = c(5, 70), marker = list(sizemode='diameter', opacity=0.5))# filter out "District of Columbia"
cv_states_today_filter <- cv_states_today %>% filter(state!="District of Columbia")
# pop_density vs. cases after filtering
cv_states_today_filter %>%
plot_ly(x = ~pop_density, y = ~cases,
type = 'scatter', mode = 'markers', color = ~state,
size = ~population, sizes = c(5, 70), marker = list(sizemode='diameter', opacity=0.5))# pop_density vs. deathsper100k
cv_states_today_filter %>%
plot_ly(x = ~pop_density, y = ~deathsper100k,
type = 'scatter', mode = 'markers', color = ~state,
size = ~population, sizes = c(5, 70), marker = list(sizemode='diameter', opacity=0.5))# Adding hoverinfo
cv_states_today_filter %>%
plot_ly(x = ~pop_density, y = ~deathsper100k,
type = 'scatter', mode = 'markers', color = ~state,
size = ~population, sizes = c(5, 70), marker = list(sizemode='diameter', opacity=0.5),
hoverinfo = 'text',
text = ~paste( paste(state, ":", sep=""), paste(" Cases per 100k: ", per100k, sep="") ,
paste(" Deaths per 100k: ", deathsper100k, sep=""), sep = "<br>")) %>%
layout(title = "Population-normalized COVID-19 deaths (per 100k) vs. population density for US states",
yaxis = list(title = "Deaths per 100k"), xaxis = list(title = "Population Density"),
hovermode = "compare")7. Explore scatterplot trend interactively using ggploty and geom_smooth
p <- ggplot(cv_states_today_filter, aes(x=pop_density, y=deathsper100k, size=population)) + geom_point() + geom_smooth()
ggplotly(p)While not totally clear, it appears that areas with smaller population density tended to have larger quantities of death per 100k. However, there is a group with lower population density that have deaths per 100k close to or at 0.
8. Multiple line chart
# Line chart for naive_CFR for all states over time using `plot_ly()`
plot_ly(recent, x = ~date, y = ~naive_CFR, color = ~state, type = "scatter", mode = "lines")# Line chart for Florida showing new_cases and new_deaths together
#lab asked to use add_layer function... function does not exist
recent %>% filter(state=="Florida") %>% plot_ly(x = ~date, y = ~new_cases, type = "scatter", mode = "lines") %>% add_trace(x = ~date, y = ~new_deaths, type = "scatter", mode = "lines") |> layout(yaxis2 = list(overlaying = "y", side = "right"))The peak in new cases comes before the peak in new deaths. It appears that as new cases are falling and heading towards a low, the peak in new deaths occurs.
9. Heatmaps
# Map state, date, and new_cases to a matrix
library(tidyr)
cv_states_mat <- recent %>% select(state, date, new_cases) %>% dplyr::filter(date>as.Date("2021-06-1"))
cv_states_mat2 <- as.data.frame(pivot_wider(cv_states_mat, names_from = state, values_from = new_cases))
rownames(cv_states_mat2) <- cv_states_mat2$date
cv_states_mat2$date <- NULL
cv_states_mat2 <- as.matrix(cv_states_mat2)
# Create a heatmap using plot_ly()
plot_ly(x=colnames(cv_states_mat2), y=rownames(cv_states_mat2),
z=~cv_states_mat2,
type="heatmap",
showscale=T)# Repeat with newper100k
cv_states_mat <- recent %>% select(state, date, newper100k) %>% dplyr::filter(date>as.Date("2021-06-15"))
cv_states_mat2 <- as.data.frame(pivot_wider(cv_states_mat, names_from = state, values_from = newper100k))
rownames(cv_states_mat2) <- cv_states_mat2$date
cv_states_mat2$date <- NULL
cv_states_mat2 <- as.matrix(cv_states_mat2)
plot_ly(x=colnames(cv_states_mat2), y=rownames(cv_states_mat2),
z=~cv_states_mat2,
type="heatmap",
showscale=T)# Create a second heatmap after filtering to only include dates every other week
filter_dates <- seq(as.Date("2021-06-01"), as.Date("2021-11-01"), by="2 weeks")
cv_states_mat <- recent %>% select(state, date, new_cases) %>% filter(date %in% filter_dates)
cv_states_mat2 <- as.data.frame(pivot_wider(cv_states_mat, names_from = state, values_from = new_cases))
rownames(cv_states_mat2) <- cv_states_mat2$date
cv_states_mat2$date <- NULL
cv_states_mat2 <- as.matrix(cv_states_mat2)
# Create a heatmap using plot_ly()
plot_ly(x=colnames(cv_states_mat2), y=rownames(cv_states_mat2),
z=~cv_states_mat2,
type="heatmap",
showscale=T)California, New York, Florida and Texas all stand out. These states were some of the only ones to have the very light ends of the color spectrum which indicates they had some of the highest numbers of new cases. When looking at the data by 100k, a few different states start to appear ,brighter. Vermont, Rhode Island and some other less populated states start to appear in those higher ends of new cases. New York still has a large number of new cases. When filtering down to only dates every 2 weeks, Florida, California, and Texas arise as the states with the highest number of cases.
10.
### For specified date
pick.date = "2021-10-15"
# Extract the data for each state by its abbreviation
cv_per100 <- recent %>% filter(date==pick.date) %>% select(state, abb, newper100k, cases, deaths) # select data
cv_per100$state_name <- cv_per100$state
cv_per100$state <- cv_per100$abb
cv_per100$abb <- NULL
# Create hover text
cv_per100$hover <- with(cv_per100, paste(state_name, '<br>', "Cases per 100k: ", newper100k, '<br>', "Cases: ", cases, '<br>', "Deaths: ", deaths))
# Set up mapping details
set_map_details <- list(
scope = 'usa',
projection = list(type = 'albers usa'),
showlakes = TRUE,
lakecolor = toRGB('white')
)
# Make sure both maps are on the same color scale
shadeLimit <- 125
# Create the map
fig <- plot_geo(cv_per100, locationmode = 'USA-states') %>%
add_trace(
z = ~newper100k, text = ~hover, locations = ~state,
color = ~newper100k, colors = 'Purples'
)
fig <- fig %>% colorbar(title = paste0("Cases per 100k: ", pick.date), limits = c(0,shadeLimit))
fig <- fig %>% layout(
title = paste('Cases per 100k by State as of ', pick.date, '<br>(Hover for value)'),
geo = set_map_details
)
fig_pick.date <- fig
#############
### Map for today's date
# Extract the data for each state by its abbreviation
cv_per100 <- cv_states_today %>% select(state, abb, newper100k, cases, deaths) # select data
cv_per100$state_name <- cv_per100$state
cv_per100$state <- cv_per100$abb
cv_per100$abb <- NULL
# Create hover text
cv_per100$hover <- with(cv_per100, paste(state_name, '<br>', "Cases per 100k: ", newper100k, '<br>', "Cases: ", cases, '<br>', "Deaths: ", deaths))
# Set up mapping details
set_map_details <- list(
scope = 'usa',
projection = list(type = 'albers usa'),
showlakes = TRUE,
lakecolor = toRGB('white')
)
# Create the map
fig <- plot_geo(cv_per100, locationmode = 'USA-states') %>%
add_trace(
z = ~newper100k, text = ~hover, locations = ~state,
color = ~newper100k, colors = 'Purples'
)
fig <- fig %>% colorbar(title = paste0("Cases per 100k: ", Sys.Date()), limits = c(0,shadeLimit))
fig <- fig %>% layout(
title = paste('Cases per 100k by State as of', Sys.Date(), '<br>(Hover for value)'),
geo = set_map_details
)
fig_Today <- fig
### Plot together
subplot(fig_pick.date, fig_Today, nrows = 2, margin = .05)Based on the lack of color on the map of the most recent data, there were more cases per 100k across the entire United States on Oct 15, 2021 than on Oct 10 2023. This makes sense as Oct 2021 was closer to the height of the pandemic than Oct 2023. Although, there still new strains of COVID continuing to create new cases, it may be that people are less concerned about it and reporting has declined as people have deemed “the pandemic is over.”